from openpyxl import Workbook
from openpyxl.chart import (
    AreaChart,  # 区域图
    Reference,  # 引用
    Series,  # 序列
)

# 创建工作簿
wb = Workbook()
# 创建工作簿
ws = wb.active

# 每一行数据
rows = [
    ['Number', 'Batch 1', 'Batch 2'],
    [2, 40, 30],
    [3, 40, 25],
    [4, 50, 30],
    [5, 30, 10],
    [6, 25, 5],
    [7, 50, 10],
]

# 向工作表中追加数据
for row in rows:
    ws.append(row)

# 创建区域图
chart = AreaChart()
# 标题
chart.title = "2D区域图案例"
# 样式
chart.style = 13
# x轴标题
chart.x_axis.title = 'x轴的标题'
# y轴标题
chart.y_axis.title = 'y轴的标题'

# 提取某一列数据
cats = Reference(ws, min_col=1, min_row=1, max_row=7)
# 提取另一列数据
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
# 向区域图中追加数据, 设置显示标题
chart.add_data(data, titles_from_data=True)
# 设置分类
chart.set_categories(cats)
# 将图表添加到指定单元格
ws.add_chart(chart, "A10")
# 保存图表
wb.save("area.xlsx")
